package org.apogee.mil.server.database.helper;

public class DatabaseQueries {
	public static final String MESSAGE_QRY = "select ul.%s, ul.%s, m.%s, to_char(m.%s, 'MM/DD/YYYY') as %s " + 
			"from user_lookup ul, messages m " + 
			"where ul.%s = m.%s " + 
			"and m.%s = %d order by %s desc";

	public static final String SERVER_CONFIG_QRY = "select %s, %s from serverConfig where %s='%s'";

	public static final String SERVER_CONFIG_ALL_QRY = "select %s, %s from serverConfig";

	public static final String GET_USERS_BY_TRAINER_ID = "select ul.%s, ul.%s, ul.%s from user_lookup ul, " +
			"trainer_trainee_lookup ttl where ttl.%s = ul.%s and ttl.%s = %d";

	public static final String GET_USERS_BY_ACCESS_LEVEL = "select u.%s ul.%s ul.%s  from user_lookup ul, " +
			"users u where ul.%s = u.%s and u.%s < %d";

	public static final String GET_ALL_USERS = "select * from user_lookup ul";

	public static final String CREATE_TABLE_QRY = "CREATE TABLE %s (\n"+
			"%s number(11) NOT NULL, "+
			"%s varchar(500) NOT NULL, "+
			"%s varchar(500) NOT NULL, "+
			"%s varchar(500) NOT NULL, "+
			"%s varchar(500) NOT NULL, "+
			"%s varchar(500) NOT NULL, "+
			"%s number(11) NOT NULL, "+
			"%s number(11) NOT NULL, "+
			"%s VARCHAR(1) NOT NULL, "+
			"%s VARCHAR(1) NOT NULL, " +  
			"%s date DEFAULT sysdate not null, "+
			"%s date NOT NULL,"+
			"PRIMARY KEY (%s)"+
			")";


	public static final String LOGIN_QRY = "select * from users where %s='%s' and %s='%s' and %s='%s'";

	public static final String UPDATE_LOGIN_QRY = "UPDATE users SET %s = sysdate WHERE %s=%s";

	public static final String GET_TRAINERS = "SELECT u.%s, u.%s, ul.%s, ul.%s "+
			"FROM users u, user_lookup ul, access_level a "+
			"WHERE u.%s = ul.%s " +
			"AND a.%s = u.%s " +
			"AND a.%s < 3";


}
